备份数据库
	1.在cmd中
	mysqldump -uroot -p yc2>d:/yc2.sql
	2.在wamp中恢复数据库
	create database yc2 charset utf8;
	use yc2;
	source d:/yc2.sql

按出生日期从大到小排序
select *from hd order by birday desc;

获得年龄最小的
select *from hd order by birday desc limit 1;
获得年龄最大的
select *from hd order by birday asc limit 1;
获得女同学年龄最大的
select *from hd where sex=2 order by birday asc limit 1;

还有多少天过生日
select sname if(c>0,concat('还有'),c,'天过生日！','你的生日已过！') from (select dayofyear(birday)-dayofyear(now())as c form hd) as f;

interval +(-) num unit
num 增加或减少的数，unit为单位如hour,minute,second,day,month,year
例：select curdate()+interval -3 day; 3天前的日期

获得20岁以内的人
select sname,birday from hd where birday>curdate()- interval 20 year;
select sname,birday from hd where birday>20000101;

对时间进行加减
select date_add(now(),interval 7 day);
select date_sub(now(),interval 7 day);

timestampdiff()时间戳函数
select sname,timestampdiff(day,birday,now()) as t from hd;

获得出生月份是偶数的同学
select sname,birday from hd where month(birday)%2=0;
获得出生日大于10号的男同学
select sname,sex,birday from hd where day(birday)>10 and sex=1;

获得男女同学数
select count(*) from hd group by sex;

按70,80,90,00后分组
select sname,birday,count(*) from hd group by left(birday,3);
select concat(mid(birday,3,1),'0后'),count(*) from hd group by left(birday,3);

按70,80,90,00后分组取得人数最多的（2条）
select concat(mid(birday,3,1),'0后') as s,count(*) as c from hd group by left(birday,3);
select concat(mid(birday,3,1),'0后') as s,count(*) as c from hd group by left(birday,3) having c=2;

作业
1. 按名子的数量(李四2个字，李长河3个字)分组，取得每组的人数
select sname,count(*) as c from hd group by length(sname);

2. 取得90后的男女同学都有多少人
select sname,sex,count(*) from hd where birday>19900101 group by sex;

3. 按姓氏分组，求得姓氏最多的学生是哪个
select concat(left(sname,1),'姓的学生最多') as l,count(*) as c from hd group by left(sname,1) order by c desc limit 1;




